package com.yabushan.form.controller;

import java.util.*;

import cn.hutool.json.JSONObject;
import com.yabushan.common.utils.SecurityUtils;
import com.yabushan.form.domain.AutoUserTable;
import com.yabushan.form.domain.FiledTitle;
import com.yabushan.form.service.IAutoUserTableService;
import com.yabushan.form.service.IFormInfosService;
import com.yabushan.form.utils.ExportExcel;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.yabushan.common.annotation.Log;
import com.yabushan.common.core.controller.BaseController;
import com.yabushan.common.core.domain.AjaxResult;
import com.yabushan.common.enums.BusinessType;
import com.yabushan.form.domain.AutoTableFileds;
import com.yabushan.form.service.IAutoTableFiledsService;
import com.yabushan.common.utils.poi.ExcelUtil;
import com.yabushan.common.core.page.TableDataInfo;

/**
 * 动态字段信息Controller
 *
 * @author yabushan
 * @date 2021-08-06
 */
@RestController
@RequestMapping("/form/fileds")
public class AutoTableFiledsController extends BaseController
{
    @Autowired
    private IAutoTableFiledsService autoTableFiledsService;

    @Autowired
    private IAutoUserTableService autoUserTableService;

    @Autowired
    private IFormInfosService formInfosService;

    /**
     * 查询动态字段信息列表
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:list')")
    @GetMapping("/list")
    public TableDataInfo list(AutoTableFileds autoTableFileds)
    {
        startPage();
        String username = SecurityUtils.getUsername();
        if(!username.equals("admin")){
            autoTableFileds.setCreatedBy(username);
        }
        List<AutoTableFileds> list = autoTableFiledsService.selectAutoTableFiledsList(autoTableFileds);
        return getDataTable(list);
    }

    @PreAuthorize("@ss.hasPermi('form:fileds:list')")
    @GetMapping("/filedlist")
    public java.lang.String getFileds(AutoTableFileds autoTableFileds)
    {
         return finishData(autoTableFileds,false,null);

    }


    private String finishData(AutoTableFileds autoTableFileds,boolean isSingle,Integer dataId){
        JSONObject jsonObj = new JSONObject();
        //获取表头
        List<AutoTableFileds> autoTableFileds1 = autoTableFiledsService.selectAutoTableFiledsList(autoTableFileds);
        List<FiledTitle> titleList  = new ArrayList<>();
        FiledTitle filedTitle=null;
        StringBuffer buffer = new StringBuffer();
        buffer.append("select ");

        filedTitle=new FiledTitle();
        filedTitle.setValue("T_ID");
        filedTitle.setName("T_ID");
        titleList.add(filedTitle);

        for(int i=0;i<autoTableFileds1.size();i++){

            filedTitle=new FiledTitle();
            filedTitle.setName(autoTableFileds1.get(i).getFiledCnName());
            filedTitle.setValue(autoTableFileds1.get(i).getFiledEnNname());
            titleList.add(filedTitle);
            if(autoTableFileds1.get(i).getFiledType().contains("datetime")){
                buffer.append("date_format(").append(autoTableFileds1.get(i).getFiledEnNname()).append(",'")
                        .append("%Y-%m-%d %H:%i:%s'").append(") as ").append(autoTableFileds1.get(i).getFiledEnNname());
            }else if(autoTableFileds1.get(i).getFiledType().contains("date")){
                buffer.append("date_format(").append(autoTableFileds1.get(i).getFiledEnNname()).append(",'")
                        .append("%Y-%m-%d'").append(") as ").append(autoTableFileds1.get(i).getFiledEnNname());
            }else{
                buffer.append(autoTableFileds1.get(i).getFiledEnNname());
            }


            buffer.append(",");

        }
        buffer.append(" T_ID ");
        buffer.append( " from ");
        jsonObj.put("title",titleList);
        //获取数据

        AutoUserTable autoUserTable = autoUserTableService.selectAutoUserTableById(autoTableFileds.getbId());
        buffer.append(autoUserTable.getbEnName());
        if(isSingle){
            //传入ID查询
            buffer.append(" where T_ID=").append(dataId);
        }
        List<Map> maps = formInfosService.dynamicsSelect(buffer.toString());
        jsonObj.put("value",maps);
        return jsonObj.toString();

    }

    /**
     * 导出动态字段信息列表
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:export')")
    @Log(title = "动态字段信息", businessType = BusinessType.EXPORT)
    @GetMapping("/export")
    public AjaxResult export(AutoTableFileds autoTableFileds)
    {
        List<AutoTableFileds> list = autoTableFiledsService.selectAutoTableFiledsList(autoTableFileds);
        ExcelUtil<AutoTableFileds> util = new ExcelUtil<AutoTableFileds>(AutoTableFileds.class);
        return util.exportExcel(list, "fileds");
    }

    /**
     * 获取动态字段信息详细信息
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:query')")
    @PostMapping(value = "/getInfo")
    public String getInfo(@RequestBody Map tableRowInfo)
    {
        Integer tId = Integer.parseInt(tableRowInfo.get("fId").toString());
        String bId = tableRowInfo.get("bId").toString();
        AutoTableFileds autoTableFileds = new AutoTableFileds();
        autoTableFileds.setbId(bId);
        String s = finishData(autoTableFileds, true, tId);
        return s;
    }

    /**
     * 新增动态字段信息
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:add')")
    @Log(title = "动态字段信息", businessType = BusinessType.INSERT)
    @PostMapping
    public AjaxResult add(@RequestBody AutoTableFileds autoTableFileds)
    {
        int i = autoTableFiledsService.insertAutoTableFileds(autoTableFileds);
        if(i==-1){
            return AjaxResult.error("表不存在，无法创建字段!");
        }else if(i==100){
            return AjaxResult.error("超过最大限制字段数，无法创建字段；请联系管理员!");
        }
        return toAjax(i);
    }

    /**
     * 修改动态字段信息
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:edit')")
    @Log(title = "动态字段信息", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult edit(@RequestBody AutoTableFileds autoTableFileds)
    {
        return toAjax(autoTableFiledsService.updateAutoTableFileds(autoTableFileds));
    }

    /**
     * 删除动态字段信息
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:remove')")
    @Log(title = "动态字段信息", businessType = BusinessType.DELETE)
	@DeleteMapping("/{fIds}")
    public AjaxResult remove(@PathVariable String[] fIds)
    {
        java.lang.String tableId = fIds[fIds.length-1].toString();
        //获取表
        AutoUserTable autoUserTable = autoUserTableService.selectAutoUserTableById(tableId);
        StringBuffer buffer = new StringBuffer();
        buffer.append("delete from ").append(autoUserTable.getbEnName()).append(" where T_ID IN(");
        for(int i =0 ;i<fIds.length-1;i++){
            //排除最后一个表名字段
            buffer.append(fIds[i]).append(",");
        }
        buffer.delete(buffer.length()-1,buffer.length()).append(")");
        formInfosService.dynamicsDelete(buffer.toString());
        return toAjax(1);
    }
    /**
     * 新增
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:edit')")
    @Log(title = "更新字段信息", businessType = BusinessType.UPDATE)
    @PostMapping("/autoAddFileds")
    public AjaxResult autoAddFileds(@RequestBody Map autoTableFileds)
    {
       // return toAjax(autoTableFiledsService.updateAutoTableFileds(autoTableFileds));

        //封装sql
        Iterator it = autoTableFileds.entrySet().iterator();
        StringBuffer tablebuffer = new StringBuffer();
        StringBuffer filedbuffer = new StringBuffer();
        StringBuffer valuebuffer = new StringBuffer();

        //识别字段类型
        AutoTableFileds fileds = new AutoTableFileds();
        fileds.setbId(autoTableFileds.get("TABLE_ID").toString());
        List<AutoTableFileds> autoTableFileds1 = autoTableFiledsService.selectAutoTableFiledsList(fileds);


        valuebuffer.append("values(");
        while (it.hasNext()) {
            Map.Entry entry = (Map.Entry) it.next();
            if(entry.getValue().equals(null) || entry.getValue().equals("")){continue;}
            if(entry.getKey().equals("TABLE_ID")){
                //获取表信息
                AutoUserTable autoUserTable = autoUserTableService.selectAutoUserTableById(entry.getValue().toString());
                tablebuffer.append("insert into ").append(autoUserTable.getbEnName()).append("(");
            }else if(entry.getKey().equals("T_ID")){
                //主键
            }else{
                //表字段
                //判断字段类型
                for(int i=0;i<autoTableFileds1.size();i++){


                    if(entry.getKey().equals(autoTableFileds1.get(i).getFiledEnNname())){
                        if(autoTableFileds1.get(i).getFiledType().contains("varchar") ||
                                autoTableFileds1.get(i).getFiledType().contains("text") || autoTableFileds1.get(i).getFiledType().contains("date")){
                            //字符串
                            valuebuffer.append("'").append(entry.getValue()).append("'");
                        }
//                        else if(autoTableFileds1.get(i).getFiledType().contains("date")){
//                            //日期
//                            Date date = DateUtils.dateTime("yyyy-MM-dd", entry.getValue().toString());
//                            valuebuffer.append(date);
//                        }else if(autoTableFileds1.get(i).getFiledType().contains("datetime")){
//                            //日期时间
//                            Date date = DateUtils.dateTime("yyyy-MM-dd HH:mm:ss", entry.getValue().toString());
//                            valuebuffer.append(date);
//                        }
                        else{
                            //数值
                            valuebuffer.append(entry.getValue().toString());
                        }

                    }
                }
                filedbuffer.append(entry.getKey()).append(",");
                valuebuffer.append(",");
            }
           // System.out.println("key:" + entry.getKey() + "   value:" + entry.getValue());
        }
        filedbuffer.delete(filedbuffer.length()-1,filedbuffer.length()).append(")");
        valuebuffer.delete(valuebuffer.length()-1,valuebuffer.length()).append(")");
        tablebuffer.append(filedbuffer).append(valuebuffer);
        formInfosService.dynamicsInsert(tablebuffer.toString());


        return toAjax(1);
    }

    /**
     * 修改
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:edit')")
    @Log(title = "新增字段信息", businessType = BusinessType.UPDATE)
    @PostMapping("/autoUpdateFileds")
    public AjaxResult autoUpdateFileds(@RequestBody Map autoTableFileds)
    {
        //封装sql
        Iterator it = autoTableFileds.entrySet().iterator();
        StringBuffer tablebuffer = new StringBuffer();
        StringBuffer firstbuffer = new StringBuffer();
        StringBuffer wherebuffer = new StringBuffer();
        Integer tId = Integer.valueOf(autoTableFileds.get("T_ID").toString());

        //识别字段类型
        AutoTableFileds fileds = new AutoTableFileds();
        fileds.setbId(autoTableFileds.get("TABLE_ID").toString());
        List<AutoTableFileds> autoTableFileds1 = autoTableFiledsService.selectAutoTableFiledsList(fileds);
        while (it.hasNext()) {
            Map.Entry entry = (Map.Entry) it.next();

            if(entry.getKey().equals("TABLE_ID")){
                //获取表信息
                AutoUserTable autoUserTable = autoUserTableService.selectAutoUserTableById(entry.getValue().toString());
                firstbuffer.append("update ").append(autoUserTable.getbEnName()).append(" set ");
            }else if(entry.getKey().equals("T_ID")){
                //主键
            }else{
                //表字段
                //判断字段类型
                for(int i=0;i<autoTableFileds1.size();i++){
                    if(entry.getKey().equals(autoTableFileds1.get(i).getFiledEnNname())){
                        if(autoTableFileds1.get(i).getFiledType().contains("varchar") ||
                                autoTableFileds1.get(i).getFiledType().contains("text") || autoTableFileds1.get(i).getFiledType().contains("date")){
                            //字符串
                            tablebuffer.append(entry.getKey()).append("=").append("'").append(entry.getValue()).append("'").append(",");
                        }
//                        else if(autoTableFileds1.get(i).getFiledType().contains("date")){
//                            //日期
//                            Date date = DateUtils.dateTime("yyyy-MM-dd", entry.getValue().toString());
//                            valuebuffer.append(date);
//                        }else if(autoTableFileds1.get(i).getFiledType().contains("datetime")){
//                            //日期时间
//                            Date date = DateUtils.dateTime("yyyy-MM-dd HH:mm:ss", entry.getValue().toString());
//                            valuebuffer.append(date);
//                        }
                        else{
                            //数值
                            tablebuffer.append(entry.getKey()).append("=").append(entry.getValue()==""?null:entry.getValue()).append(",");
                        }

                    }
                }

            }
            // System.out.println("key:" + entry.getKey() + "   value:" + entry.getValue());
        }
        tablebuffer.delete(tablebuffer.length()-1,tablebuffer.length());
        wherebuffer.append(" where T_ID=").append(tId);
        firstbuffer.append(tablebuffer).append(wherebuffer);
        formInfosService.dynamicsInsert(firstbuffer.toString());


        return toAjax(1);
    }


    /**
     * 导出动态字段信息列表
     */
    @PreAuthorize("@ss.hasPermi('form:fileds:export')")
    @Log(title = "动态数据导出", businessType = BusinessType.EXPORT)
    @GetMapping("/exportAutoData")
    public AjaxResult exportAutoData(AutoTableFileds autoTableFileds) throws Exception {
        //获取表头
        List<AutoTableFileds> autoTableFiledsList = autoTableFiledsService.selectAutoTableFiledsList(autoTableFileds);
        AutoTableFileds autoTableFileds2=new AutoTableFileds();
        autoTableFileds2.setFiledEnNname("T_ID");
        autoTableFileds2.setFiledCnName("T_ID");
        autoTableFiledsList.add(autoTableFileds2);
        //获取表数据
        List<Map> result = finishDataMap(autoTableFileds, false, null);
        List<java.lang.String[]> dataList = new ArrayList<java.lang.String[]>();
        java.lang.String[] objs = null;

        java.lang.String[] title = null;
        title = new java.lang.String[autoTableFiledsList.size()];
        objs = new java.lang.String[autoTableFiledsList.size()];
        for (int i =0;i<autoTableFiledsList.size();i++){
            title[i]=autoTableFiledsList.get(i).getFiledCnName().toString();
        }


        //封装数据
        for (int i = 0; i < result.size(); i++) {
            HashMap<String, java.lang.String> data = (HashMap<String, java.lang.String>) result.get(i);
            objs = new java.lang.String[autoTableFiledsList.size()];
            for(int j=0;j<autoTableFiledsList.size();j++){
                String filedEnNname = autoTableFiledsList.get(j).getFiledEnNname();
                String value = String.valueOf(data.get(filedEnNname));
                objs[j] =value;
            }
            dataList.add(objs);
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("动态表数据");

        ExportExcel ex = new ExportExcel("动态表数据", title,dataList );
        String export = ex.export(workbook, sheet, 1);
        return  AjaxResult.success(export);
        // return AjaxResult.success();
     //  ExcelUtil<String[]> util = new ExcelUtil<String[]>(String[].class);
        //return util.exportExcel(dataList, "fileds");
    }


    private   List<Map> finishDataMap(AutoTableFileds autoTableFileds,boolean isSingle,Integer dataId){
        JSONObject jsonObj = new JSONObject();
        //获取表头
        List<AutoTableFileds> autoTableFileds1 = autoTableFiledsService.selectAutoTableFiledsList(autoTableFileds);
        List<FiledTitle> titleList  = new ArrayList<>();
        FiledTitle filedTitle=null;
        StringBuffer buffer = new StringBuffer();
        buffer.append("select ");

        filedTitle=new FiledTitle();
        filedTitle.setValue("T_ID");
        filedTitle.setName("T_ID");
        titleList.add(filedTitle);

        for(int i=0;i<autoTableFileds1.size();i++){

            filedTitle=new FiledTitle();
            filedTitle.setName(autoTableFileds1.get(i).getFiledCnName());
            filedTitle.setValue(autoTableFileds1.get(i).getFiledEnNname());
            titleList.add(filedTitle);
            if(autoTableFileds1.get(i).getFiledType().contains("datetime")){
                buffer.append("date_format(").append(autoTableFileds1.get(i).getFiledEnNname()).append(",'")
                        .append("%Y-%m-%d %H:%i:%s'").append(") as ").append(autoTableFileds1.get(i).getFiledEnNname());
            }else if(autoTableFileds1.get(i).getFiledType().contains("date")){
                buffer.append("date_format(").append(autoTableFileds1.get(i).getFiledEnNname()).append(",'")
                        .append("%Y-%m-%d'").append(") as ").append(autoTableFileds1.get(i).getFiledEnNname());
            }else{
                buffer.append(autoTableFileds1.get(i).getFiledEnNname());
            }


            buffer.append(",");

        }
        buffer.append(" T_ID ");
        buffer.append( " from ");
        jsonObj.put("title",titleList);
        //获取数据

        AutoUserTable autoUserTable = autoUserTableService.selectAutoUserTableById(autoTableFileds.getbId());
        buffer.append(autoUserTable.getbEnName());
        if(isSingle){
            //传入ID查询
            buffer.append(" where T_ID=").append(dataId);
        }
        List<Map> maps = formInfosService.dynamicsSelect(buffer.toString());
       return maps;

    }
}
